﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using trangbaocao.Models;
using System.Data;
using System.Data.OracleClient;
using Microsoft.Reporting.WebForms;
namespace trangbaocao.Reports.Fuel.FuelStations
{
    public partial class FuelStations : System.Web.UI.Page
    {
        StationsModel StationsModel = new StationsModel();
        Main_StationsModel Main_StationsModel = new Main_StationsModel();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                if (Session != null && Convert.ToInt32(Session["quyen_id"])>2)
                {
                    Radio1.Visible = false;
                }
                List<Main_StationsModel> main_StationsModel = Main_StationsModel.getMain_Stations();
                List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(main_StationsModel[0].id));
                cbtrungtam.DataSource = main_StationsModel;
                cbtrungtam.DataTextField = "name";
                cbtrungtam.DataValueField = "id";
                cbtrungtam.DataBind();

                cbtram.DataSource = stationModel;
                cbtram.DataTextField = "name";
                cbtram.DataValueField = "id";
                cbtram.DataBind();

                cbnam.DataSource = getYear();
                cbnam.DataTextField = "nam";
                cbnam.DataValueField = "nam";
                cbnam.DataBind();

                for (int i = 1; i <= 12; i++)
                    cbthang.Items.Add(i.ToString());
                cbthang.Items.FindByValue(DateTime.Now.Month.ToString()).Selected = true;
            }
        }

        protected void Radio1_CheckedChanged(object sender, EventArgs e)
        {
            if (Radio1.Checked)
            {
                cbtrungtam.Enabled = false;
                cbtram.Enabled = false;
            }
            if(Radio2.Checked)
            {
                cbtrungtam.Enabled = true;
                cbtram.Enabled = true;                
            }
        }
        
        protected void cbtrungtam_SelectedIndexChanged(object sender, EventArgs e)
        {
            List<StationsModel> stationModel = StationsModel.getStationsByMain_Stations(Convert.ToInt32(cbtrungtam.SelectedValue));
            cbtram.DataSource = stationModel;
            cbtram.DataTextField = "name";
            cbtram.DataValueField = "id";
            cbtram.DataBind();
        }
        public DataSet getYear()
        {
            string sql = "SELECT distinct nam  from fuel_input order by nam desc";
            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }

        public DataSet getAllFuelStations()
        {
            string sql = "";
            string nam = cbnam.SelectedValue;
            string thang = cbthang.SelectedValue;
            string trung_tam_id = cbtrungtam.SelectedValue;
            string tram_id = cbtram.SelectedValue;

            string sql_tram = "";
            if (radio_tram.Checked)
                sql_tram = " and b.id='"+tram_id+"'";
            if(Radio2.Checked)
            {                
                //sql = "select b.id ,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu from";
                //sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
                //sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where  device_stations_id is null and nam=" + nam + " and thang<=" + thang + " group by stations_id) A, fuel B";
                //sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + nam;
                //sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where c.id=" + trung_tam_id +sql_tram+ " and b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";

                sql = "select a.stations_id,a.ten_tram,a.dia_chi,a.ten_trung_tam,a.nhap_trong_thang so_dien_kw,b.luy_ke_so_dien_kw,a.tien_dien_thang,b.luy_ke_tien,a.ngay_nhap,a.ghi_chu from";
                sql += " (select b.id stations_id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.nhap_trong_thang,a.tong_tien tien_dien_thang,a.ngay_nhap,a.ghi_chu  ";
                sql += " from(select a.stations_id,b.don_gia,b.nhap_trong_thang,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(Select B.Id, A.stations_id, A.thang ";
                sql += " From (select stations_id,max(thang) thang from fuel_input Where fuel_type_id=1 and nam=" + nam + " and thang<=" + thang + " group by stations_id";
                sql += " ) A, fuel_input B where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + nam;
                sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where c.id="+trung_tam_id+sql_tram+" and  b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name) a,";
                sql += " (select stations_id,sum(tong_tien) luy_ke_tien,sum(nhap_trong_thang) luy_ke_so_dien_kw  from fuel_input where  fuel_type_id=1 and nam=" + nam + " and thang<=" + thang + " group by stations_id) b where a.stations_id=b.stations_id(+)";
            }
            if (Radio1.Checked)
            { 
                //sql = "select b.id ,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu from";
                //sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
                //sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where  device_stations_id is null and nam="+nam+" and thang<="+thang+" group by stations_id) A, fuel B";
                //sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + nam;
                //sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";

                sql = "select a.stations_id,a.ten_tram,a.dia_chi,a.ten_trung_tam,a.nhap_trong_thang so_dien_kw,b.luy_ke_so_dien_kw,a.tien_dien_thang,b.luy_ke_tien,a.ngay_nhap,a.ghi_chu from";
                sql += " (select b.id stations_id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.nhap_trong_thang,a.tong_tien tien_dien_thang,a.ngay_nhap,a.ghi_chu  ";
                sql += " from(select a.stations_id,b.don_gia,b.nhap_trong_thang,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(Select B.Id, A.stations_id, A.thang ";
                sql += " From (select stations_id,max(thang) thang from fuel_input Where fuel_type_id=1 and nam=" + nam + " and thang<=" + thang + " group by stations_id";
                sql += " ) A, fuel_input B where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam="+nam;
                sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where  b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name) a,";
                sql += " (select stations_id,sum(tong_tien) luy_ke_tien,sum(nhap_trong_thang) luy_ke_so_dien_kw  from fuel_input where fuel_type_id=1 and nam=" + nam + " and thang<=" + thang + " group by stations_id) b where a.stations_id=b.stations_id(+)";
            }
            
            OracleConnection conn = General.ConnectDataBase();
            OracleCommand omd = new OracleCommand(sql, conn);
            OracleDataAdapter oda = new OracleDataAdapter(omd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
            }
            catch
            {
                System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
            }
            oda.Fill(ds, "0");
            conn.Close();
            return ds;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ReportViewer1.Reset();
            //DataTable dt = GetCustomerList();

            Main_Sort_DeviceModel Main_Sort_DeviceModel = new Main_Sort_DeviceModel();

            List<Main_Sort_DeviceModel> main_Sort_DeviceModel = Main_Sort_DeviceModel.getChungloaithietbi();

            ReportViewer1.LocalReport.ReportPath = "Reports/Fuel/FuelStations/ReportFuelStations.rdlc";
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("fuelstations_dataset", getAllFuelStations().Tables[0]));

            ReportViewer1.DataBind();
            ReportViewer1.LocalReport.Refresh();
        }

        protected void radio_tram_group_CheckedChanged(object sender, EventArgs e)
        {
            if (radio_tram_group.Checked)
            {
                cbtram.Enabled = false;
            }
            if (radio_tram.Checked)
            {
                cbtram.Enabled = true;
            }
        }
        //protected void Radio2_CheckedChanged(object sender, EventArgs e)
        //{
        //    cbtrungtam.Enabled = false;
        //    cbtram.Enabled = false;
        //}
    }
}